InfoMagic Internet Tools 1995 April/Internet Tools.iso/infoserv/gopher/Gopher_Conference_94/Papers/SQLEvents.Z/SQLEvents

Text File | 1994-04-20 | 11.0 KB | 372 lines

City

╖    Stateid

╖    Cityid

╖    City




State

╖    Stateid

╖    Statename
Building

╖    Buildingid

╖    cityid

╖    building

╖    address

╖    postalcode
Location

╖    Loccode

╖    Buildingid

╖    Room
Events

╖    eventid

╖    Title

╖    Category

╖    Costlevel

╖    Costdesc

╖    Loccode

╖    Moreinfo
Eventtimes

╖    Eventid

╖    Edate

╖    Time

╖    Length
Eventinfo

╖    Eventid

╖    About
Entity Relationship Diagram for the
Events and Locations Database
A Gopher Events Database
using Sqlgopher.

Paul Lindner

In this paper we describe the implementation of a database of Events. The data is stored in an Oracle SQL database.
Access to this database (additions, deletions, browsing and searching) is done using Gopher clients. A special Gopher to
SQL gateway provides the necessary glue to bind it all together.

April 13, 1994

1.0    Introduction

At one time the University of Minnesota had a centralized
list of upcoming events on campus, a secretary in the Uni-
versity Relations office typed them all up and ran an ad in
the student newspaper. Due to budget cuts this labor inten-
sive and expensive procedure was discontinued in 1991.

The need for a centralized list of events was still there
however. Thus, in fall 1992 Distributed Computing Ser-
vices started offering organizations on campus the oppor-
tunity to post events on a centralized gopher server using
the FTP file transfer protocol.

This scheme worked fairly well: it offered a central repos-
itory for people to store events, the cost was low (all data
entry was done by the event sponsors) and the full text
indexing was sufficient for searching.

There were some problems with this approach however.
You could only browse the list of events in one way, sorted
by event sponsor. Event entries were inconsistent despite
specific instructions given to the event sponsors. The full
text search was inconsistent, you couldn't separate out
events based on cost, location or category very easily.
Using FTP to add data was cumbersome at best.

Starting in spring 1994 we're starting a new, more sophis-
ticated central repository of events. The new system offers
the following features:

╖    Events can be sorted by Date, Location, Category, or
Price.

╖    Online adding of events using Gopher+ forms.

╖    Sophisticated form based searches.

The events are stored in an Oracle SQL based database.
This allows for high consistency and quick access. A
gopher to SQL gateway permits access to the database
using the Gopher protocol.

2.0    Why an Events Database?

A campus/metropolitan wide events database brings the
community many benefits:

╖    Event goers can browse and search for events of inter-
est more easily than poring through the student news-
paper and a pile of flyers.

╖    Event schedulers can find opportunities for collabora-
tion with other organizations.

╖    Event schedulers can plan events so they don't coin-
cide with other events.

╖    Information dissemination is the key to getting com-
muter students involved in their campus community;
the University of Minnesota doesn't have the benefit of
being a small `college town.' Thus the database helps
foster a sense of student community on a large campus.

3.0    The Database

The database consists of two major portions, the Locations
database, and the Events database. Both portions are inte-
gral to having a well managed events database.

3.1    Locations

The Locations database contains information about vari-
ous venues where an event occurs, such as the Building
name, Room name/number, street address, postal code,
etc.

By having a very accurate locations database people can
search the database for events that are located in a specific
areas. For instance, you could search for events "nearby"
that are in the local postal/zip code. Or, you could browse
by state, city, building, and then room.

3.2    Events

The Events database contains information about the actual
event, such as the title, category, cost, contacts for more
information, date, time, length, and description, etc.

You can browse the Events portion of the database in
many ways. You can browse by the category of events, or
by date. A special feature allows you to get a listing of all
the events for the current day, and the week ahead.

There are many ways to search the events database. The
database is keyword indexed, so you can find events that
have a certain word in their description. Or, you can fill
out a form that lets you find events with a high degree of
accuracy. This form allows you to specify the exact
matches you want for any of the fields in the database. For
example, you could search the database for all events that
cost less than $5, are in the category `CONCERT' and are
in the zip code `55455'. This search can be put on a menu
item if one wishes. (You might name this item `Cheap
Concerts on the Minneapolis Campus')

4.0    A Tour of the Database

Let's follow the travels of a hypothetical student named
George. George is bored. He has a bundle of cash burning
a hole in his pocket. He wants to do something.

So George fires up his SLIP connection and gets con-
nected to the U of M network and launches his favorite
gopher client, Turbogopher for his Macintosh.

He gets a screen like this:

FIGURE 1.    Getting Into Gopher

George is one smart dude, so he uses his built in bookmark
to access the Database of events. He then gets a number of
choices. He can browse the database by type, location or
date or he can search for a specific event.

FIGURE 2.    Starting Screen for the Events Database

George can quickly browse the Events for Today items.
They make great bookmarks, since they change every day.
The first item returns the items listed in a directory, this is
best for browsing. The second item is a file containing all
the events for the day. This is handy for printing out.

George is looking to the weekend however, so he selects
"Events for the next 7 days".

FIGURE 3.    Events for the Next 7Days

And, lo and behold, he sports "Saturday Night Fever."!
George notices that there are two showings coming up, so
he clicks on the item and gets the description of the event:

FIGURE 4.    Saturday Night Fever Event

Cool! George gets his leisure suit ready for the cleaners
and looks further for something later in the week to
do.This time he knows that there's a performance at
Northrup that he wants to see, so he selects the Multiple
field search and searches for the Building Northrup and
the category DANCE.

FIGURE 5.    Multiple Field Search

Then he gets a list of items that match his specified crite-
ria, all DANCE events in buildings named Northrup.

FIGURE 6.    Dance Events at Northrup

George looks through the items there and finds out that the
Miami City Ballet is playing for the low low price of
$16.50.



5.0    Event and Location Data
Management

A number of forms are defined for adding data to the
events database. The first form is for adding a Building/
room to the list of locations.

The gateway software does some intelligent checks to
insure that the city is in the database and checks for
matches in the database.

FIGURE 7.    Adding a New Location

The second form is used to enter information about the
event. Again, a number of consistency checks are done.

FIGURE 8.    Adding a New Event

For events that have more than one showing (movies,
plays, etc.) we have another form for adding a time to a
current event. This form can be used as many times as nec-
essary to add Show Times to an existing event.

FIGURE 9.    Adding an Additional Date and Time

6.0    Future plans

The Locations database will eventually interface with a
database of Maps and Coordinates, allowing one to
browse locations graphically. In the future this database
might contain three dimensional renderings of geography.

Currently the Events database is geared towards singular
events (i.e. events that occur only once or twice.) This is
because we store the beginning time and length in a table.
This is inefficient for repeating events such as long run-
ning movies, plays and art exhibits. A separate database or
redesign may be required for these events.

Another database being considered is a `Personnel' data-
base. People could use this to create their own personal
schedules. This would also allow you to `sign' up for an
event, and browse a list of attendees for an event.

A rating system for events could be established, allowing
someone to `vote' for a specific event. You could then
search for highly rated events.

7.0    Technical Details

7.1    Internet Gopher and the Gopher Protocol

Internet Gopher is an information system used to publish
and organize information on servers distributed across the
Internet. Initially developed at the University of Minne-
sota in early 1991, it has spread to over 4800 sites world-
wide as of December 1993.

The Gopher system is a client-server system that can be
used to build a Campus Wide Information System
(CWIS). Clients, which browse and search information are
available for most major platforms (Macintosh, DOS,
Windows, Unix, VMS, MVS, VM/CMS, OS/2). Servers,
which translate and publish information, are also available
for all of the platforms mentioned above.

This client-server architecture uses the Internet Gopher
Protocol. The Gopher protocol has been described as "bru-
tally simple." It is based on a web/tree metaphor of files
and directories. Its basic primitives are a list directory
transaction, a retrieve file transaction and a search for
directory entries transaction.

7.2    SQL Database Details. Entities and
Relationships

The following page contains a diagram of the tables con-
tained in the database. Each table is represented by it's
name followed by a bulleted list of the columns. Arrows
connect columns that reference other tables.

A number of views are defined to make searching the data-
base easier. The view Eventview contains the most popular
fields for searching and joins the location, building,
events, eventtimes, and eventinfo tables together. An
Eventinserter view is used to insert items into the data-
base. The Eventplace view joins together the location,
building, and city.

A number of indexes are also created, to increase the per-
formance of searches.

7.3    The Gateway Software

The software that brings allows access to the database is
Gophersql. Gophersql was developed by the University of
Minnesota to allow Internet Gopher Clients access to a
SQL database such as Oracle or Sybase.

The SQL gateway allows the Gopher Client to:

╖    View the tables of a database as a Gopher directory

╖    View the columns of a given table as a Gopher direc-
tory

╖    View the contents of a column as a Gopher directory

╖    View records as formatted text.

╖    View/import records as tab-separated-values

╖    Add records to a table.

╖    Search the table by filling out a Gopher+ form.

The gateway is available via anonymous ftp from

boombox.micro.umn.edu

via anonymous ftp from the directory

/pub/gopher/Unix/gopher-gateways/gophersql

The files used to construct this events database are
included in the distribution.

7.4    Accessing the Gateway

To access the Events database you can connect to the
machine arcwelder.micro.umn.edu on port 70.